/**
 * Created by xc on 2016/05/12.
 */

var common = require('../../common');
var pool = require('../index');
var mysql = require('mysql');
var ItbBaseModel = require('./ItbBaseModel');


// Schema 结构
var ItbTemplateModel = function(obj){
    ItbBaseModel.call(this,obj);

  //`tmpl_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '模版id',
  //`base_tmpl_id` bigint(20) unsigned DEFAULT NULL COMMENT '母版id',
  //`tmpl_name` varchar(100) DEFAULT NULL COMMENT '模版名',
  //`master` int(1) NOT NULL DEFAULT '0' COMMENT '0模版 1母版',
  //`user_id` bigint(20) NOT NULL COMMENT '用户id',
  //`user_type` int(1) NOT NULL DEFAULT '0' COMMENT '0后台',
  //`publish` int(1) NOT NULL DEFAULT '0' COMMENT '0制作中; 1已完成',
  //`data` mediumtext COMMENT '模版内容（除page以外的信息）',
  //`vip` int(2) NOT NULL DEFAULT '0' COMMENT '0普通模版, 10vip专属模版',
  //`order` int(3) NOT NULL DEFAULT '999' COMMENT '显示顺序',
  //`ver` int(10) NOT NULL DEFAULT '1' COMMENT '版本号',
  //`create_time` datetime NOT NULL,
  //`update_time` datetime NOT NULL,
  //`update_by` varchar(50) NOT NULL DEFAULT '',
  //`del_flg` int(1) NOT NULL DEFAULT '0',

};
ItbTemplateModel.prototype = new ItbBaseModel();

// 取得表名
ItbTemplateModel.prototype.getTableName = function(){
    return 'itb_template';
}
// 取得查询语句中where 后面匹配主键的条件（需要各派生类进行重载）
ItbTemplateModel.prototype.getWhereConditionOfKeyEqual = function(){
    return 'tmpl_id='+this.tmpl_id;
}

//------------------------------------------------------------------
/* 获取最新娱乐用文档.
 * 参数:callback:回调，参数
 * */
ItbTemplateModel.prototype.getLatestEntDoc=function(callback){
    var THIS = this;
    var tableName = this.getTableName();

    // 从mysql中查询
    pool.pool.getConnection(function(err, connection) {
        var sql =
            ' SELECT t.tmpl_id '+
            ' FROM itb_template t, '+
            '      itb_doc_type_r t1, '+
            '      itb_doc_type t2 '+
            ' WHERE t2.category = 2 '+// 用途
            '   AND t2.type_index = 7 '+// 节日娱乐
            '   AND t1.type_id = t2.type_id '+
            '   AND t.tmpl_id = t1.doc_id '+
            '   AND t.user_type = 0 '+// 模版数据
            '   AND t.del_flg = 0 '+
            '   AND t1.del_flg = 0 '+
            '   AND t2.del_flg = 0 '+
            ' ORDER BY t.update_time DESC '+
            ' LIMIT 0,1 ';

        console.log(sql);

        // Use the connection
        connection.query(sql, function(error, items) {
            // And done with the connection.
            connection.release();

            // 回调
            callback(error, items);
        });

    });
}

//------------------------------------------------------------------
/* 获取母版数据
 * 参数: id或ids
 * 参数:callback:回调，参数
 * */
ItbTemplateModel.prototype.getMasterTmpl=function(callback, skipCnt, pageSize){
    var THIS = this;
    var tableName = this.getTableName();

    // 从mysql中查询
    pool.pool.getConnection(function(err, connection) {
        var sql =
            ' SELECT * FROM itb_template t '+
            ' WHERE t.del_flg = 0 '+
            '   AND t.user_type = 0 '+// 后台数据
            '   AND t.master = 1 ';// 母版
        if (THIS.id) {
            sql += ' AND t.tmpl_id = ' + THIS.id;
        } else if (THIS.ids) {
            sql += ' AND t.tmpl_id IN (' + THIS.ids + ') ';
        }
        sql += ' ORDER BY t.update_time DESC ';

        if(typeof skipCnt!='undefined' && skipCnt != null && typeof pageSize!='undefined' && pageSize != null){
            sql += " limit " + Number(skipCnt) + "," + Number(pageSize);
        }

        console.log(sql);

        // Use the connection
        connection.query(sql, function(error, items) {
            // And done with the connection.
            connection.release();

            // 回调
            callback(error, items);
        });

    });
}

//------------------------------------------------------------------
/* 获取所有已发布模版
 * 参数: id或ids
 * 参数:callback:回调，参数
 * */
ItbTemplateModel.prototype.getAllPublishedTmpl=function(callback, skipCnt, pageSize){
    var THIS = this;
    var tableName = this.getTableName();

    // 从mysql中查询
    pool.pool.getConnection(function(err, connection) {
        var sql =
            ' SELECT t.tmpl_id, t.user_id FROM itb_template t '+
            ' WHERE t.del_flg = 0 '+
            '   AND t.publish = 1 '+// 已发布
            '   AND t.master = 0 ';// 模版
        if (THIS.id) {
            sql += ' AND t.tmpl_id = ' + THIS.id;
        } else if (THIS.ids) {
            sql += ' AND t.tmpl_id IN (' + THIS.ids + ') ';
        }
        sql += ' ORDER BY t.update_time DESC ';

        if(typeof skipCnt!='undefined' && skipCnt != null && typeof pageSize!='undefined' && pageSize != null){
            sql += " limit " + Number(skipCnt) + "," + Number(pageSize);
        }

        console.log(sql);

        // Use the connection
        connection.query(sql, function(error, items) {
            // And done with the connection.
            connection.release();

            // 回调
            callback(error, items);
        });

    });
}

//------------------------------------------------------------------
/* 根据type获取ppt信息
 * */
ItbTemplateModel.prototype.getPptInfoByType=function(typeM,typeS,skipCnt,pageSize,callback,bWithoutVip){
    var THIS = this;
    var tableName = this.getTableName();

    // 从mysql中查询
    pool.pool.getConnection(function(err, connection) {
        var sql = ' SELECT x1.*, CASE WHEN x2.tmpl_price IS NULL THEN "免费" ELSE x2.tmpl_price END AS tmpl_price, '+
            ' IFNULL(st.apply_cnt,0) AS apply_cnt, IFNULL(st.show_apply_cnt,0) AS show_apply_cnt, '+
            ' IFNULL(st.view_cnt,0) AS view_cnt, IFNULL(st.show_view_cnt,0) AS show_view_cnt '+
            ' FROM '+
            ' (SELECT t.tmpl_id, t.tmpl_name, p.thumb_s AS thumb, t.update_time, t.vip '+
            ' FROM itb_template t, itb_tmpl_page p '+
            ' WHERE t.del_flg = 0 AND t.master=0 AND t.publish=1 '+
            ' AND p.del_flg=0 AND p.tmpl_id = t.tmpl_id AND p.page_index=0 '+
            ' AND t.tmpl_id IN (SELECT tr.doc_id '+
            ' FROM itb_doc_type tp, ITB_DOC_TYPE_R tr '+
            ' WHERE tp.del_flg=0 AND tr.del_flg=0 AND tr.type_id=tp.type_id ';
        if(typeM>0) {
            sql += ' AND tp.category =' + typeM;
        }
        if(typeS>0) {
            sql += ' AND tp.type_id =' + typeS;
        }
        if(bWithoutVip) {// 不包括vip模版
            sql += ' AND t.vip=0 ';
        }

        sql += ') ) x1 '+
            ' LEFT JOIN '+
            ' (SELECT tp.tmpl_id, MIN(tp.price), '+
            ' concat(TRUNCATE(tp.price/100, 2),"元") AS tmpl_price '+
            ' FROM itb_tmpl_price tp '+
            ' WHERE tp.del_flg=0 AND tp.type IN (3) GROUP BY tp.tmpl_id) x2 '+
            ' ON x2.tmpl_id = x1.tmpl_id ';

        // 取统计数据 added 2017-5-12
        sql += ' LEFT JOIN itb_tmpl_stats st '+
            ' ON st.del_flg=0 AND st.type=3 AND st.tmpl_id=x1.tmpl_id ';

        sql += ' ORDER BY x1.update_time DESC ';

        if(typeof skipCnt!='undefined' && skipCnt != null
            && typeof pageSize!='undefined' && pageSize != null && pageSize>0){
            sql += " limit " + Number(skipCnt) + "," + Number(pageSize);
        }

        console.log(sql);

        // Use the connection
        connection.query(sql, function(error, items) {
            // And done with the connection.
            connection.release();

            // 回调
            callback(error, items);
        });

    });
}

//------------------------------------------------------------------
/* 获取基本信息.
 * @param callback,参数如下：
 *          error
 *          data:数组结构，数组元素如下：
 *                  designId
 *                  designName
 *                  typeL
 *                  typeM
 *                  typeS
 *                  vip
 *                  sort 创建时的时间戳作为排序字段
 * */
ItbTemplateModel.prototype.getBaseInfo=function(callback){
    var THIS = this;
    var tableName = this.getTableName();

    // 从mysql中查询
    pool.pool.getConnection(function(err, connection) {
        var sql =
            ' SELECT t.tmpl_id as designId, '+
            '       t.`tmpl_name` as designName, '+
            ' 		func_parse_string_array(func_ppt_type_map(dr.`type_id`),0) as typeL, '+
            '       func_parse_string_array(func_ppt_type_map(dr.`type_id`),1) as typeM, '+
            '       func_parse_string_array(func_ppt_type_map(dr.`type_id`),2) as typeS, '+
            '       t.vip as vip, '+
            ' 	    UNIX_TIMESTAMP(t.`create_time`) as sort '+
            ' FROM itb_doc_type_r dr, '+
            '      itb_template t '+
            ' WHERE dr.doc_id= '+ THIS.tmpl_id +
            '   AND dr.del_flg=0 '+
            '   AND t.tmpl_id=dr.`doc_id` '+
            '   AND t.del_flg=0 ';

        console.log(sql);

        // Use the connection
        connection.query(sql, function(error, items) {
            // And done with the connection.
            connection.release();

            // 回调
            callback(error, items);
        });

    });
}


//------------------------------------------------------------------
/* 获取基本信息.
 * @param callback,参数如下：
 *          error
 *          data:数组结构，数组元素如下：
 *                  designId
 *                  designName
 *                  typeL
 *                  typeM
 *                  typeS
 *                  vip
 *                  sort 创建时的时间戳作为排序字段
 * */
ItbTemplateModel.prototype.getBaseInfo2=function(callback){
    var THIS = this;
    var tableName = this.getTableName();

    // 从mysql中查询
    pool.pool.getConnection(function(err, connection) {
        var sql =
            ' SELECT t.tmpl_id as designId, '+
            '       t.`tmpl_name` as designName, '+
            ' 		func_parse_string_array(func_ppt_type_map(dr.`type_id`),0) as typeL, '+
            '       func_parse_string_array(func_ppt_type_map(dr.`type_id`),1) as typeM, '+
            '       func_parse_string_array(func_ppt_type_map(dr.`type_id`),2) as typeS, '+
            '       t.vip as vip, '+
            ' 	    UNIX_TIMESTAMP(t.`create_time`) as sort ,'+
            ' 			(select group_concat(k.key_id) from `tg_keyword_r` r ,`tg_keyword` k where k.`key_id`=r.`key_id` and k.del_flg=0 and r.del_flg=0 and r.`doc_id`= '+ THIS.tmpl_id + '  and k.type=1 and r.type_id=3 ) as industries, '+
            ' 			(select group_concat(k.key_id) from `tg_keyword_r` r ,`tg_keyword` k where k.`key_id`=r.`key_id` and k.del_flg=0 and r.del_flg=0 and r.`doc_id`= '+ THIS.tmpl_id + '  and k.type=2 and r.type_id=3 ) as scenes, '+
            ' 			(select group_concat(k.key_id) from `tg_keyword_r` r ,`tg_keyword` k where k.`key_id`=r.`key_id` and k.del_flg=0 and r.del_flg=0 and r.`doc_id`= '+ THIS.tmpl_id + '  and k.type=4 and r.type_id=3 ) as colors, '+
            ' 			(select group_concat(k.key_id) from `tg_keyword_r` r ,`tg_keyword` k where k.`key_id`=r.`key_id` and k.del_flg=0 and r.del_flg=0 and r.`doc_id`= '+ THIS.tmpl_id + '  and k.type=3 and r.type_id=3 ) as styles, '+
            ' 			(select group_concat(k.key_id) from `tg_keyword_r` r ,`tg_keyword` k where k.`key_id`=r.`key_id` and k.del_flg=0 and r.del_flg=0 and r.`doc_id`= '+ THIS.tmpl_id + '  and k.type=10 and r.type_id=3 ) as customs '+
            ' FROM itb_doc_type_r dr, '+
            '      itb_template t '+
            ' WHERE dr.doc_id= '+ THIS.tmpl_id +
            '   AND dr.del_flg=0 '+
            '   AND t.tmpl_id=dr.`doc_id` '+
            '   AND t.del_flg=0 ';

        console.log(sql);

        // Use the connection
        connection.query(sql, function(error, items) {
            // And done with the connection.
            connection.release();

            // 回调
            callback(error, items);
        });

    });
}
//------------------------------------------------------------------
/* 获取搜索信息.
 * @param callback,参数如下：
 *          error
 *          data:数组结构，数组元素如下：
 *                     "typeL":8,
 *                     "id":1804,
 *                     "vip":0,
 *                     "type_desc":"PPT",
 *                     "name":"黄色简约促销活动微商模版",
 *                     "w":"720",
 *                     "h":"1280",
 *                     "thumb":"http://itbour-generate.oss-cn-hangzhou.aliyuncs.com/image/U401486/2017/07/11/140032041_7lxjx0x0FaNVw0xh0RjB/0.jpg",
 *                     "author":"高梦梦",
 *                     "time":"2017-07-27 11:33",
 *                     "useCounts":11,
 *                     "price":"免费"
 * */
ItbTemplateModel.prototype.getSearchInfo=function(callback){
    var THIS = this;
    var tableName = this.getTableName();

    // 从mysql中查询
    pool.pool.getConnection(function(err, connection) {
        var sql =
            ' select 3 as typeL, '+
            ' 		t.tmpl_id as id, '+
            ' 		t.vip as vip , '+
            ' 		"PPT" as type_desc, '+
            ' 		t.tmpl_name as name, '+
            ' 		1600 as w, '+
            ' 		900 as h, '+
            ' 		p.`thumb_s` as thumb, '+
            ' 		u.`nickname` as author, '+
            ' 		t.create_time as `time`, '+
            ' 		s.show_apply_cnt as useCounts, '+
            ' 		case  t.vip when 10 then "VIP专享" else "免费" end as price '+
            ' from itb_template t, '+
            ' 	itb_tmpl_page p, '+
            ' 	itb_user u, '+
            ' 	itb_tmpl_stats s '+
            ' where t.tmpl_id= '+ THIS.tmpl_id +
            ' and t.del_flg=0 '+
            ' and t.tmpl_id=p.tmpl_id '+
            ' and p.del_flg=0 '+
            ' and p.page_index=0 '+
            ' and t.user_id=u.user_id '+
            ' and u.del_flg=0 '+
            ' and s.tmpl_id=t.tmpl_id '+
            ' and s.del_flg=0 ';

        console.log(sql);

        // Use the connection
        connection.query(sql, function(error, items) {
            // And done with the connection.
            connection.release();

            // 回调
            callback(error, items);
        });

    });
}

//------------------------------------------------------------------
// 导出
module.exports = ItbTemplateModel;